I'm running into some problems with arrays in my SQL which're giving me fits.
I've got some SQL statements that I'm issuing from my app using the
PQexecParams() C call. All the parameters are passed in as literal
string parameters (that is, the paramTypes array entry for each
placeholder is set to 0) letting the engine convert. The statements
look something like:
INSERT INTO foo (bar, baz, xyzzy) VALUES ($1, $2, ARRAY[$3, $4, $5])
where the xyzzy column is an array. (I can't change this, it's a
holdover from the ISAM database scheme we're moving off of) When I
issue the command, the error I get back is:
DB error is: ERROR: column "xyzzy" is of type numeric[] but
expression is of type text[]
HINT: You will need to rewrite or cast the expression.
I've tried scattering to_number calls in the SQL, but this makes the
SQL look really messy, I'd much rather have it all handled on the
back end for consistency, and it really feels like I'm doing
something wildly wrong here anyway.
So, what am I doing wrong? Why isn't the back end converting the
parameters for array fields the way it does non-array fields? Is
there something simple and straightforward I can do to make this work
that I'm just missing here?
--
Dan
--------------------------------------it's like this-------------------
Dan Sugalski even samurai
dan@sidhe.org have teddy bears and even
teddy bears get drunk
